﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Table;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;


namespace Project.Library
{
    public class SQLAzure : IData
    {
        private System.Data.SqlClient.SqlConnection SQLConnection;


        private bool Exists(String TableName)
        {
            bool retval;
            SQLConnection.Open();
            using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("SELECT * FROM sysobjects WHERE xtype = 'U' AND name = '{0}'",TableName);
                using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
                {
                    retval = reader.HasRows;
                }
                cmd.Dispose();
            }
            SQLConnection.Close();
            return retval;
        }




        private int CreateIfNotExists<T>(String TableName, ref T oClass)
        {
            if (!Exists(TableName))
            {
                SQLConnection.Open();
                String strTemp = "";
                using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    strTemp = String.Format("create table {0}({1});", TableName, "{0} {1}{2} {3}");
                    foreach (System.Reflection.PropertyInfo x in oClass.GetType().GetProperties())
                    {
                        String strType = "";
                        switch (x.PropertyType.Name)
                        {
                            case "DateTimeOffset":
                                strType = "nvarchar(max)";
                                break;
                            case "String":
                                strType = "nvarchar(max)";
                                break;
                            case "List`1":
                                strType = "varbinary(max)";
                                break;
                            case "Byte[]":
                                strType = "varbinary(max)";
                                break;
                            default:
                                strType = x.PropertyType.Name;
                                break;
                        }
                        strTemp = String.Format(strTemp, x.Name, strType, ",", "{0} {1}{2} {3}");
                    }
                    strTemp = strTemp.Replace(", {0} {1}{2} {3}", "");
                    cmd.CommandText = strTemp;
                    cmd.ExecuteNonQuery();
                }
                SQLConnection.Close();
                return 1;
            }
            else
            {
                return 2;
            }

        }

        private T Retrieve<T>(string TableName, String PartitionKey, String RowKey) where T : new()
        {

            SQLConnection.Open();
            using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("select * from {0} where PartitionKey='{1}' AND RowKey='{2}';", TableName, PartitionKey, RowKey);
                using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        T retval = new T();
                        while (reader.Read())
                        {
                            foreach (System.Reflection.PropertyInfo x in retval.GetType().GetProperties())
                            {

                                switch (x.PropertyType.Name)
                                {
                                    case "DateTimeOffset":
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, DateTimeOffset.Parse(reader[x.Name].ToString()));
                                        break;
                                    case "String":
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, reader[x.Name]);
                                        break;
                                    case "List`1":
                                        //retval.GetType().GetProperty(x.Name).SetValue(retval, (List<Stduent>reader[x.Name]);
                                        break;
                                    case "Byte[]":
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, reader[x.Name]);
                                        break;
                                    default:
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, reader[x.Name]);
                                        break;
                                }

                            }
                        }
                        cmd.Dispose();
                        SQLConnection.Close();
                        return retval;
                    }
                }
            }
            SQLConnection.Close();
            return default(T);
        }

        private List<T> GetData<T>(string TableName, string PartitionKey = "", string RowKey = "") where T : new()
        {

            SQLConnection.Open();
            List<T> oList = new List<T>();
            using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                if (PartitionKey != "" && RowKey != "")
                {
                    cmd.CommandText = String.Format("select * from {0} where PartitionKey='{1}' AND RowKey='{2}';", TableName, PartitionKey, RowKey);
                }
                else
                    if (PartitionKey == "" && RowKey != "")
                    {
                        cmd.CommandText = String.Format("select * from {0} where RowKey='{1}';", TableName, RowKey);
                    }
                    else
                        if (PartitionKey != "" && RowKey == "")
                        {
                            cmd.CommandText = String.Format("select * from {0} where PartitionKey='{1}';", TableName, PartitionKey);
                        }
                        else
                        {
                            cmd.CommandText = String.Format("select * from {0} ;", TableName);
                        }
                using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            T retval = new T();
                            foreach (System.Reflection.PropertyInfo x in retval.GetType().GetProperties())
                            {

                                switch (x.PropertyType.Name)
                                {
                                    case "DateTimeOffset":
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, DateTimeOffset.Parse(reader[x.Name].ToString()));
                                        break;
                                    case "String":
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, reader[x.Name]);
                                        break;
                                    case "List`1":
                                        //retval.GetType().GetProperty(x.Name).SetValue(retval, (List<Stduent>reader[x.Name]);
                                        break;
                                    case "Byte[]":
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, reader[x.Name]);
                                        break;
                                    default:
                                        retval.GetType().GetProperty(x.Name).SetValue(retval, reader[x.Name]);
                                        break;
                                }
                            }
                            oList.Add(retval);
                        }
                    }
                    else
                    {
                        SQLConnection.Close();
                        return default(List<T>);
                    }
                }
            }
            SQLConnection.Close();
            return oList;
        }


        private int Insert<T>(string TableName, ref T oClass)
        {
            SQLConnection.Open();
            String strTemp = "";
            int retval;
            using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
            {
                strTemp = String.Format("insert into {0} values({1});", TableName, "{0}{1}{2}");
                foreach (System.Reflection.PropertyInfo x in oClass.GetType().GetProperties())
                {

                    switch (x.PropertyType.Name)
                    {
                        case "DateTimeOffset":
                            String strValueDT = "'" + oClass.GetType().GetProperty(x.Name).GetValue(oClass).ToString() + "'";
                            strTemp = String.Format(strTemp, strValueDT, ",", "{0}{1}{2}");
                            break;
                        case "String":
                            String strValue = "'" + oClass.GetType().GetProperty(x.Name).GetValue(oClass).ToString() + "'";
                            strTemp = String.Format(strTemp, strValue, ",", "{0}{1}{2}");
                            break;
                        case "List`1":
                            System.IO.MemoryStream ms = new MemoryStream();
                            BinaryFormatter bf = new BinaryFormatter();
                            bf.Serialize(ms, oClass.GetType().GetProperty(x.Name).GetValue(oClass));
                            Object olValue = ms.ToArray();
                            strTemp = String.Format(strTemp, "@" + x.Name, ",", "{0}{1}{2}");
                            cmd.Parameters.Add("@" + x.Name, SqlDbType.Binary, ((byte[])olValue).Length).Value = olValue;
                            break;
                        case "Byte[]":
                            Object obValue = oClass.GetType().GetProperty(x.Name).GetValue(oClass);
                            strTemp = String.Format(strTemp, "@" + x.Name, ",", "{0}{1}{2}");
                            cmd.Parameters.Add("@" + x.Name, SqlDbType.Binary, ((byte[])obValue).Length).Value = obValue;
                            break;
                        default:
                            String defValue = "'" + oClass.GetType().GetProperty(x.Name).GetValue(oClass).ToString() + "'";
                            strTemp = String.Format(strTemp, defValue, ",", "{0}{1}{2}");
                            break;
                    }
                }
                strTemp = strTemp.Replace(",{0}{1}{2}", "");
                cmd.CommandText = strTemp;
                retval = cmd.ExecuteNonQuery();
            }
            SQLConnection.Close();
            return retval;
        }

        private int DeleteIfExists(String TableName, string PartitionKey = "", string RowKey = "")
        {
            SQLConnection.Open();
            int retval;
            using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                if (PartitionKey != "" && RowKey != "")
                {
                    cmd.CommandText = String.Format("delete from {0} where PartitionKey='{1}' AND RowKey='{2}';", TableName, PartitionKey, RowKey);
                }
                else
                    if (PartitionKey == "" && RowKey != "")
                    {
                        cmd.CommandText = String.Format("delete from {0} where RowKey='{1}';", TableName, RowKey);
                    }
                    else
                        if (PartitionKey != "" && RowKey == "")
                        {
                            cmd.CommandText = String.Format("delete from {0} where PartitionKey='{1}';", TableName, PartitionKey);
                        }
                        else
                        {
                            cmd.CommandText = String.Format("delete from {0} ;", TableName);
                        }
                retval = cmd.ExecuteNonQuery();
            }
            SQLConnection.Close();
            return retval;
        }


        private int Replace<T>(string TableName, ref T oClass, String PartitionKey, String RowKey)
        {
            SQLConnection.Open();
            String strTemp = "";
            int retval;
            using (System.Data.SqlClient.SqlCommand cmd = SQLConnection.CreateCommand())
            {
                strTemp = String.Format("update {0} set {1};", TableName, "{0}{1}{2}");
                foreach (System.Reflection.PropertyInfo x in oClass.GetType().GetProperties())
                {

                    switch (x.PropertyType.Name)
                    {
                        case "DateTimeOffset":
                            String strValueDT = x.Name + "=" + "'" + oClass.GetType().GetProperty(x.Name).GetValue(oClass).ToString() + "'";
                            strTemp = String.Format(strTemp, strValueDT, ",", "{0}{1}{2}");
                            break;
                        case "String":
                            String strValue = x.Name + "=" + "'" + oClass.GetType().GetProperty(x.Name).GetValue(oClass).ToString() + "'";
                            strTemp = String.Format(strTemp, strValue, ", ", "{0}{1}{2}");
                            break;
                        case "List`1":
                            MemoryStream ms = new MemoryStream();
                            BinaryFormatter bf = new BinaryFormatter();
                            bf.Serialize(ms, oClass.GetType().GetProperty(x.Name).GetValue(oClass));
                            Object olValue = ms.ToArray();
                            strTemp = String.Format(strTemp, x.Name + "=" + "@" + x.Name, ", ", "{0}{1}{2}");
                            cmd.Parameters.Add("@" + x.Name, SqlDbType.Binary, ((byte[])olValue).Length).Value = olValue;
                            break;
                        case "Byte[]":
                            Object obValue = oClass.GetType().GetProperty(x.Name).GetValue(oClass);
                            strTemp = String.Format(strTemp, x.Name + "=" + "@" + x.Name, ", ", "{0}{1}{2}");
                            cmd.Parameters.Add("@" + x.Name, SqlDbType.Binary, ((byte[])obValue).Length).Value = obValue;
                            break;
                        default:
                            String defValue = x.Name + "=" + "'" + oClass.GetType().GetProperty(x.Name).GetValue(oClass).ToString() + "'";
                            strTemp = String.Format(strTemp, defValue, ", ", "{0}{1}{2}");
                            break;
                    }
                }
                strTemp = strTemp.Replace(", {0}{1}{2}", " where PartitionKey='{0}' AND RowKey='{1}'");
                strTemp = String.Format(strTemp, PartitionKey, RowKey);
                cmd.CommandText = strTemp;
                retval = cmd.ExecuteNonQuery();
            }
            SQLConnection.Close();
            return retval;
        }

        public int Init(String ConnectionString)
        {
            try
            {
                if (ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString == "")
                {
                    return 0;
                }
                SqlConnectionStringBuilder csBuilder;
                csBuilder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);
                SQLConnection = new SqlConnection(csBuilder.ToString());
                return 1;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int Create<T>(string TableName, ref T oClass) where T : Microsoft.WindowsAzure.Storage.Table.TableEntity, new()
        {
            try
            {
                CreateIfNotExists(TableName, ref oClass);

                /*
                // Retrive a single Entity
                TableOperation oTableOperation = TableOperation.Retrieve<T>(oClass.PartitionKey, oClass.RowKey);
                TableResult oTableResult = table.Execute(oTableOperation);

                 */

                //Assign Retrived Result to a Object
                T oResult = Retrieve<T>(TableName, oClass.PartitionKey, oClass.RowKey);

                //Update if not null
                if (oResult != null)
                {
                    return 2;
                }

                Insert<T>(TableName, ref oClass);

                return 1;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<T> Read<T>(string TableName, String PartitionKey = "", String RowKey = "") where T : Microsoft.WindowsAzure.Storage.Table.TableEntity, new()
        {
            try
            {

                if (!Exists(TableName))
                {
                    return null;
                }

                return GetData<T>(TableName, PartitionKey, RowKey);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int Update<T>(string TableName, ref T oClass) where T : Microsoft.WindowsAzure.Storage.Table.TableEntity
        {
            try
            {

                if (!Exists(TableName))
                {
                    return 0;
                }

                return Replace<T>(TableName, ref oClass, oClass.PartitionKey, oClass.RowKey);

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int Delete(string TableName)
        {
            try
            {

                if (!Exists(TableName))
                {
                    return 0;
                }

                DeleteIfExists(TableName);
                return 1;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int Delete<T>(string TableName, string PartitionKey, string RowKey) where T : Microsoft.WindowsAzure.Storage.Table.TableEntity
        {
            try
            {

                if (!Exists(TableName))
                {
                    return 0;
                }

                return DeleteIfExists(TableName, PartitionKey, RowKey);

            }
            catch (Exception e)
            {
                throw e;
            }
        }

    }
}
